(ns ura.data.UserInfo
  (:require 
   [cheshire.core :as json]
   [taoensso.telemere :as t]
   [clojure.java.io :as io]
   [clojure.data.csv :as csv]
   [ura.data.LogData :as ulog]
   [ura.data.Data :refer [execute executeSql checkDataNull nextId upcaseKey]]
   [tick.core :as tk]
   )
  (:use tupelo.core)
  )

(def userKeys [:username :password :code :sex :age :state :description])


(defn assocUserData [user]
  (let [id (:id user)
        infos (json/decode (:infos user))
        roleInfoList (executeSql {:select [:r.*]
                                  :from [[:USER_TO_ROLE :ur] [:ROLE_INFO :r]]
                                  :where [:and
                                          [:= :ur.ROLE_ID :r.ID]
                                          [:= :ur.USER_ID id]
                                          ]
                                  })
        roleIds (mapv :id roleInfoList)
        orgInfoList (executeSql {:select [:o.*]
                                 :from [[:USER_TO_ORG :uo] [:ORG_INFO :o]]
                                 :where [:and
                                         [:= :uo.ORG_ID :o.ID]
                                         [:= :uo.USER_ID id]
                                         ]
                                 })
        orgIds (mapv :id orgInfoList)
        ]
    (assoc user
           :infos infos
           :roleIds roleIds
           :roleInfoList roleInfoList
           :orgInfoList orgInfoList
           :orgIds orgIds
           )
    )
  )

(defn queryRoleList [id]
  (executeSql {:select [:r.*]
               :from [[:USER_TO_ROLE :ur] [:ROLE_INFO :r]]
               :where [:and
                       [:= :ur.ROLE_ID :r.ID]
                       [:= :ur.USER_ID id]
                       ]
               })

  )

(defn queryOrgList [id]
  (executeSql {:select [:o.*]
               :from [[:USER_TO_ORG :uo] [:ORG_INFO :o]]
               :where [:and
                       [:= :uo.ORG_ID :o.ID]
                       [:= :uo.USER_ID id]
                       ]
               })
  )

(defn login [req]
  (t/log!  ["login " req])
  (let [name (:name req)
        code (:code req)
        res  (if (or (nil? name) (empty? name))
               (executeSql {:select :* :from :USER_INFO :where [:= :CODE code]})
               (executeSql {:select :* :from :USER_INFO :where [:= :USERNAME name]})
               )
        userData (first res)
        ]
    (t/log!  ["get userinfo: " userData])
    (let [result (if (some? userData)
                   (.equals (:password userData) (:passwd req))
                   false
                   )
          username (:username userData) ]
      (if result
        (ulog/saveLog {:level "info" :model_name "ura" :username username :message "user login success"})
        (ulog/saveLog {:level "warning" :model_name "ura" :username username :message "user login failed" :data (str req)})
        )
      {:login result :userinfo userData}
      )
  ))

(defn logout [req]
  (t/log!  ["logout:" req])
  (let [username (:username req)]
    (ulog/saveLog {:level "info" :model_name "ura" :username username :message "user logout success"})
    )
  )

(defn queryUserById [uid]
  (first (executeSql {:select [:*]
                      :from :USER_INFO
                      :where [:= :ID uid]}))
  )
(defn queryUserByName [name]
  (executeSql {:select [:*]
                      :from :USER_INFO
                      :where [:= :USERNAME name]})
  )

(defn queryUserInfo  "查询用户信息"
  [params]
  (t/log! ["query user by" params])
  (let [username (:username params)
        code (:code params)
        data (executeSql {:select :*
                          :from :USER_INFO
                          :where [:and
                                  (when (checkDataNull username) [:like :USERNAME (str "%" username "%")])
                                  (when (checkDataNull code) [:like :CODE (str "%" code "%")])
                                  ]
                          })
        data1 (doall (map assocUserData data))
        ]
    data1
    )
  )

(defn queryUserInfoPage "分页查询用户信息"
  [params]
  (t/log! {:data params} "query user data")
  (let [search-params (:search-params params)
        username (:username params)
        code (:code params)

        current (Integer/parseInt (:current params))
        page-size (Integer/parseInt (:page-size params))
        total (Integer/parseInt (:total params))

        sqlWhere [:and
                  (when (checkDataNull username) [:like :USERNAME (str "%" username "%")])
                  (when (checkDataNull code) [:like :CODE (str "%" code "%")])
                  ]
        
        total (:total (first (executeSql {:select [[:%count.* :total]]
                           :from :USER_INFO
                           :where sqlWhere
                           })))

        data (executeSql {:select :*
                          :from :USER_INFO
                          :where sqlWhere
                          :limit page-size
                          :offset (* (- current 1) page-size)
                          })
        data1 (mapv assocUserData data)
        ]
    {:data  data1
     :page {:total total
            :current current
            :page-size page-size
            }
     }
    )
  )

(defn updateUserPassword [req]
  (t/log!  ["update user password : " req])
  (let [
        [usr] (queryUserByName (:username req))
        pwd (:PASSWORD usr)
        id (:ID usr)
        oldpwd (:oldpwd req)
        newpwd (:newpwd req)
        ]
    (t/log!  ["update user info : " usr pwd id newpwd])
    (if (.equals oldpwd pwd)
      (->
       (executeSql {:update :USER_INFO :set {:PASSWORD newpwd} :where [:= :ID id]})
       (as-> res (if (== res 1) "success" "update fail"))
       (as-> resp {:resp resp})
       )
      {:resp "invalid old password"}
      )
    )
  )


(defn update-role-org [userId roleIds orgIds]
  (if (< 0 (count orgIds))
    (let [data (doall (map #(vector userId %) (set orgIds)))]
      (t/log! ["save user--org data: " data])
      (executeSql {:delete-from [:USER_TO_ORG] :where [:= :USER_ID userId]})
      (executeSql {:insert-into [:USER_TO_ORG] :values data})
      )
    (t/log!  ["no user--org data"])
    )

  (if (< 0 (count roleIds))
    (let [data (map #(vector userId %) (set roleIds))]
      (t/log!  ["saved user--role data: " data])
      (executeSql {:delete-from [:USER_TO_ROLE] :where [:= :USER_ID userId]})
      (executeSql {:insert-into [:USER_TO_ROLE] :values data})
      )
    (t/log! ["no user--role data"])
    )
  )




(defn updateUser [userInfoData]
  (t/log! ["update user data request: " userInfoData])
  (let [id (:id userInfoData)
        userInfo (upcaseKey (merge
                             (select-keys userInfoData userKeys)
                             {:infos (json/encode (:infos userInfoData))}
                             ))
        ]
    (try
      (executeSql {:update :USER_INFO
                   :set userInfo
                   :where [:= :ID id]
                   })
      (update-role-org id (:roleIds userInfoData) (:orgIds userInfoData))
      (catch Exception e
        (t/log!  e)
        (.printStackTrace e)
        )
      )
    )
  )

(defn insertUser [userInfoData]
  (t/log! {:data userInfoData} "insert user data")
  (let [newId (nextId "USER_INFO_SEQ")
        userInfo (upcaseKey (merge
                             (select-keys userInfoData userKeys)
                             {:infos (json/encode (:infos userInfoData))}
                             {:id newId}
                             ))
        ]
    (executeSql {:insert-into [:USER_INFO]
                         :values [userInfo]
                         })
    (update-role-org newId (:roleIds userInfoData) (:orgIds userInfoData))
    )
  )

(defn saveUser [userInfoData]
  (t/log! ["save user data request: " userInfoData])
  (let [ isExist (first (queryUserById (:id userInfoData))) ]
    (if isExist
      (updateUser userInfoData)
      (insertUser userInfoData)
      )
    )
  )


(defn deleteUser [id]
  (t/log! ["delete user by id: " id])
  (let [user (queryUserById id)]
    (if (= "admin" (:username user))
      (t/log! ["can not delete 'admin' user"])
      (executeSql {:delete-from [:USER_INFO] :where [:= :ID id]})
      )
    )
  )


(defn queryUserAuth [userid]
  (executeSql {:select [:ai.*]
               :from [[:AUTH_INFO :ai] [:USER_TO_ROLE :ur] [:ROLE_TO_AUTH :ra]]
               :where [:and
                       [:= :ur.USER_ID userid]
                       [:= :ur.ROLE_ID :ra.ROLE_ID]
                       [:= :ra.AUTH_ID :ai.ID]
                       ]
               })
  )

(defn query-user-auth-simple [userid]
  (let [data (queryUserAuth userid)]
    (->> data
         (mapv (fn [{code :code name :name}]
               {(keyword code) name}
               ))
         (reduce merge)
         )
    )
  )



;; for import / export

(import
 org.apache.poi.hssf.usermodel.HSSFWorkbook
 java.io.FileInputStream
 java.io.File
 )


(defn importUserInfo [params]
  (let [
        type (:type params)
        user (:user params)
        fileName (-> params :file :filename)
        file (-> params :file :tempfile)
        size (-> params :file :size)
        ]
    (t/log! {:data [type user fileName file size]} "import user data: ")
    (let [tmpfilepath (:path (bean file))
          custom-path (str "./data/" type "/" fileName)
          ]
      (io/make-parents (io/file custom-path))
      (io/copy (io/file tmpfilepath) (io/file custom-path))
      (spy custom-path)
      (with-open [fis (new FileInputStream (new File custom-path))]
        
        (let [book (new HSSFWorkbook fis)
              sheet (.getSheetAt book 0)
              rowIter (.iterator sheet)
              ]
          (spy sheet)
          (.next rowIter) ;; skip header
          (while (.hasNext rowIter)
            (let [row (.next rowIter)
                  cellIter (.cellIterator row)
                  username (.getStringCellValue (.next cellIter))
                  password (.getStringCellValue (.next cellIter))
                  code (.getStringCellValue (.next cellIter))
                  sex (.getStringCellValue (.next cellIter))
                  age (.getStringCellValue (.next cellIter))
                  description (.getStringCellValue (.next cellIter))
                  infos (.getStringCellValue (.next cellIter))
                  user (first (executeSql {:select :* :from :USER_INFO :where [:= :CODE code]}))
                  isExist  (not (nil? user))
                  userId (:id user)
                  ]
              (if isExist
                (updateUser {:id userId
                             :username username
                             :password password
                             :code code
                             :sex sex
                             :age age
                             :description description
                             :infos infos
                             })
                (insertUser {:username username
                             :password password
                             :code code
                             :sex sex
                             :age age
                             :description description
                             :infos infos
                             })
                ))))))
      {:result "success"}
      )
  )

(defn exportData []
  (t/log! "export data")
  (let [book     (new HSSFWorkbook)
        sheet    (.createSheet book "用户数据")

        head     ["姓名" "密码" "账号" "性别" "年龄" "说明" "信息"]
        headrow  (.createRow sheet 0)

        data     (queryUserInfo nil)
        len      (count data)
        
        time     (tk/format (tk/formatter "yyyyMMdd-HHmmss") (tk/date-time))
        filepath "./web/export/"
        filename (str "user-" time ".xlsx")
        file     (str filepath filename)
        out      (new java.io.FileOutputStream (new java.io.File file))
        ]
    
    (doseq [ i (range 0 7)
            :let [v (head i)]
            ]
      (.setCellValue (.createCell headrow i) v)
      )
    
    (doseq [i (range len)
            :let [dt (nth data i)
                  row  (.createRow sheet (+ i 1))
                  ]]
      (.setCellValue (.createCell row 0) (:username dt))
      (.setCellValue (.createCell row 1) (:password dt))
      (.setCellValue (.createCell row 2) (:code dt))
      (.setCellValue (.createCell row 3) (:sex dt))
      (.setCellValue (.createCell row 4) (str (:age dt)))
      (.setCellValue (.createCell row 5) (:comment dt))
      (.setCellValue (.createCell row 6) (:infos dt))
      )
    
    (.write book out)
    (.close out)
    (t/log! {:data file} "generated export file")
    [file filename]
    )
  )


